package com.duleme.dao;

import com.duleme.common.Config;
import com.duleme.common.DBUtils;
import com.duleme.entity.UserInfo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserInfoDao {
    //插入用户信息
    public boolean insert(UserInfo userInfo) {
        String sql = "INSERT INTO userinfo (name,birthday,sex,phoneNumber,emailAddress,id) VALUES(?,?,?,?,?,?)";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, userInfo.getName());
            ps.setDate(2, new java.sql.Date(userInfo.getBirthday().getTime()));
            ps.setInt(3, userInfo.getSex());
            ps.setString(4, userInfo.getPhoneNumber());
            //数据类型转换，将java.util.Date 转为java.sql.Date
            ps.setString(5, userInfo.getEmailAddress());
            ps.setString(6, userInfo.getId());
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection, ps, null);
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    //根据id查询用户信息
    public UserInfo findByUserId(int id) {
        String sql = "SELECT * FROM userinfo WHERE userId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                UserInfo userInfo = new UserInfo();
                userInfo.setSex(rs.getInt("sex"));
                userInfo.setPhoneNumber(rs.getString("phoneNumber"));
                userInfo.setName(rs.getString("name"));
                userInfo.setEmailAddress(rs.getString("emailAddress"));
                userInfo.setBirthday(rs.getDate("birthday"));
                userInfo.setId(rs.getString("id"));
                userInfo.setUserId(rs.getInt("userId"));
                DBUtils.close(connection, ps, rs);
                return userInfo;
            }
            DBUtils.close(connection, ps, rs);
            return null;
        } catch (Exception e) {
            e.getStackTrace();
            return null;
        }
    }


    public UserInfo findById(String id) {
        String sql = "SELECT * FROM userinfo WHERE id=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                UserInfo userInfo = new UserInfo();
                userInfo.setSex(rs.getInt("sex"));
                userInfo.setPhoneNumber(rs.getString("phoneNumber"));
                userInfo.setName(rs.getString("name"));
                userInfo.setEmailAddress(rs.getString("emailAddress"));
                userInfo.setBirthday(rs.getDate("birthday"));
                userInfo.setId(rs.getString("id"));
                userInfo.setUserId(rs.getInt("userId"));
                DBUtils.close(connection, ps, rs);
                return userInfo;
            }
            DBUtils.close(connection, ps, rs);
            return null;
        } catch (Exception e) {
            e.getStackTrace();
            return null;
        }
    }

    //查询所有用户信息
    public List<UserInfo> findAllUserInfo() {
        String sql = "SELECT * FROM userinfo";
        try {
            List<UserInfo> userInfos = new ArrayList<>();
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                UserInfo userInfo = new UserInfo();
                userInfo.setSex(rs.getInt("sex"));
                userInfo.setPhoneNumber(rs.getString("phoneNumber"));
                userInfo.setName(rs.getString("name"));
                userInfo.setEmailAddress(rs.getString("emailAddress"));
                userInfo.setBirthday(rs.getDate("birthday"));
                userInfo.setId(rs.getString("id"));
                userInfo.setUserId(rs.getInt("userId"));
                userInfos.add(userInfo);
            }
            DBUtils.close(connection, ps, rs);
            return userInfos;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean initializeUserInfo(int userId){
        String sql="UPDATE userinfo SET name=?,birthday=?,sex=?,phoneNumber=?,emailAddress=?,id=? WHERE userId=?";
        try {
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setString(1,"");
            ps.setDate(2,new java.sql.Date(new Date().getTime()));
            ps.setInt(3,1);
            ps.setString(4,"");
            ps.setString(5,"");
            ps.setString(6,"");
            ps.setInt(7,userId);
            if(ps.executeUpdate()>0){
                DBUtils.close(connection,ps,null);
                return true;
            }
            DBUtils.close(connection,ps,null);
            return false;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }
    //根据id删除用户信息
    public boolean deleteById(int id) {
        String sql = "DELETE FROM userinfo WHERE userId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection, ps, null);
            return false;
        } catch (Exception e) {
            e.getStackTrace();
            return false;
        }
    }

    public boolean updateIdAndName(int userId, String id, String name) {
        String sql = "UPDATE userinfo SET id=?,name=? WHERE userId=? ";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, id);
            ps.setString(2, name);
            ps.setInt(3, userId);
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection, ps, null);
            return false;
        } catch (Exception e) {
            e.getStackTrace();
            return false;
        }
    }

    public boolean updateBirthday(Date birthday, int userId) {
        String sql = "UPDATE userinfo SET birthday=? WHERE userId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setDate(1, new java.sql.Date(birthday.getTime()));
            ps.setInt(2, userId);
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection, ps, null);
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }


    public boolean updateSex(int sex, int userId) {
        String sql = "UPDATE userinfo SET sex=? WHERE userId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, sex);
            ps.setInt(2, userId);
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection, ps, null);
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean updatePhoneNumber(String phoneNumber, int userId) {
        String sql = "UPDATE userinfo SET phoneNumber=? WHERE userId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, phoneNumber);
            ps.setInt(2, userId);
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection, ps, null);
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    //生成一张只包含userid的空表
    public boolean insert(int usrID){
        String sql = "INSERT INTO userinfo (userid,birthday) VALUES (?,?);";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1,usrID);
            ps.setDate(2,new java.sql.Date(new Date().getTime()));
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection,ps,null);
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    public boolean updateEmail(String email, int userId) {
        String sql = "UPDATE userinfo SET emailAddress=? WHERE userId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, email);
            ps.setInt(2, userId);
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection, ps, null);
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean updateUserInfo(UserInfo userInfo) {
        String sql = "UPDATE userinfo SET birthday=?,sex=?,phoneNumber=?,emailAddress=? WHERE userId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps= connection.prepareStatement(sql);
            ps.setDate(1, new java.sql.Date(userInfo.getBirthday().getTime()));
            ps.setInt(2, userInfo.getSex());
            ps.setString(3, userInfo.getPhoneNumber());
            ps.setString(4, userInfo.getEmailAddress());
            ps.setInt(5, userInfo.getUserId());
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection, ps, null);
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public static void main(String[] args) {
        UserInfoDao dao = new UserInfoDao();
//        System.out.print(dao.findById(100001).getName());
//        UserInfo userInfo = new UserInfo();
//        userInfo.setBirthday(new Date());
//        userInfo.setUserId(100001);
//        userInfo.setSex(2);
//        userInfo.setBirthday(new Date());
//        userInfo.setEmailAddress("1041455555@qq.com");
//        userInfo.setPhoneNumber("13322222356");
//        dao.updateUserInfo(userInfo);
        List<UserInfo> infos=dao.findAllUserInfo();
        System.out.println(infos.size());

    }
}
